package ru.hh.school.persistence;

import ru.hh.school.PrivateMessage;
import ru.hh.school.UserData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBTestHelper {
    private final Connection connection;

    public DBTestHelper(Connection connection) {
        this.connection = connection;
    }

    public void insertUser(int id, String login, String password, int roomId) throws Exception {
        final String sql = "INSERT INTO hh_0001_team01.chat_user (id, login, password, room_id) VALUES (?, ?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ps.setString(2, login);
        ps.setString(3, password);
        ps.setInt(4, roomId);
        ps.executeUpdate();
    }

    public UserData getUserDataById(int id) throws Exception {
        final String sql = "SELECT id, login, room_id FROM hh_0001_team01.chat_user WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return new UserData(rs.getInt(1), rs.getString(2), rs.getInt(3));
        } else {
            return null;
        }
    }

    public UserData getUserDataByLogin(String login) throws Exception {
        final String sql = "SELECT id, login, room_id FROM hh_0001_team01.chat_user WHERE login = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, login);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return new UserData(rs.getInt(1), rs.getString(2), rs.getInt(3));
        } else {
            return null;
        }
    }

    public void insertPrivateMessage(long id, String text, int recipientId) throws Exception {
        final String sql = "INSERT INTO hh_0001_team01.private_message (id, text, recipient_id) VALUES (?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        ps.setString(2, text);
        ps.setInt(3, recipientId);
        ps.executeUpdate();
    }

    public PrivateMessage getPrivateMessageById(long id) throws Exception {
        final String sql = "SELECT id, text, recipient_id FROM hh_0001_team01.private_message WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return new PrivateMessage(rs.getLong(1), rs.getString(2), rs.getInt(3));
        } else {
            return null;
        }
    }

    public PrivateMessage getPrivateMessageByTextAndRecipientId(String text, int recipientId) throws Exception {
        final String sql = "SELECT id, text, recipient_id FROM hh_0001_team01.private_message WHERE text = ? AND recipient_id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, text);
        ps.setInt(2, recipientId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return new PrivateMessage(rs.getLong(1), rs.getString(2), rs.getInt(3));
        } else {
            return null;
        }
    }
}